package reporting;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import main.EventHandler;

//import org.joda.time.DateTime;

import dao.Query;

public class ReportHandler {
	
	private static Query dbhandler;
	public ReportHandler() {
		dbhandler = new Query();
		dbhandler.openConnection();
	}
	
//input: start and end date for the report period
//output: profit for each hotel
	public String[][] getProfit(String firstDate, String endDate) {
//		DateTime current = GUI_Event_Handler.createDate(date);
//		DateTime firstOfCurrent = current.withDayOfMonth(1);
//		DateTime firstOfNext = firstOfCurrent.plusMonths(1);
//		
//		String firstDate = firstOfCurrent.toString().substring(0, 10);
//		String endDate = firstOfNext.toString().substring(0, 10);
		
		String query = new String("SELECT h.name, sum(b.total_cost) " +
								  "FROM Hotel h, Booking b, Room r " +
								  "WHERE b.fk_room = r.rid and b.status = 'CHECKED_OUT' and r.fk_hotel = h.hid " +
								  "and b.date_out < '"+endDate+"' and b.date_out >= '"+firstDate+"' " +
								  "GROUP BY h.name");
		System.out.println(query);
		ResultSet r = dbhandler.executeSelectStatement(query);
		ArrayList<String[]> s = new ArrayList<String[]>();
		try {
			while (r.next()) {
				String[] temp = {r.getString(1), r.getString(2)};
				s.add(temp);
			}
		} catch (SQLException e) {
			System.err.println("ERROR: Failed to read query result.");
		}
		
		return s.toArray(new String[0][0]);
	}
	
//input: date to be reported, hid of the hotel
//output: hotel name, # of occupied rooms, total # of rooms
	public String[][] getOccupancyRate(String startDate, String endDate) {
	
		String query = new String("SELECT h.name AS 'Hotel', count(distinct r.rid) AS 'Occupied Rooms', ( " +
				"SELECT count( r1.rid ) " +
				"FROM Room r1 " +
				"WHERE r1.fk_hotel = h.hid " +
				") AS 'Total Rooms' " +
				"FROM Hotel h, Booking b, Room r, Room r1 " +
				"WHERE b.fk_room = r.rid " +
				"AND b.status != 'CANCELLED' " +
				"AND r.fk_hotel = h.hid " +
				"AND b.date_out > '"+startDate+"' " +
				"AND b.date_in <= '"+endDate+"' " +
				"GROUP BY h.name; " );
		
		System.out.println(query);
		ResultSet r = dbhandler.executeSelectStatement(query);
		ArrayList<String[]> s = new ArrayList<String[]>();
		try {
			while (r.next()) {
				String[] temp = {r.getString(1), r.getString(2), r.getString(3)};
				s.add(temp);
			}
		} catch (SQLException e) {
			System.err.println("ERROR: Failed to read query result.");
		}
		
		return s.toArray(new String[0][0]);
	}
	public static void main(String[] args) {
		ReportHandler rh = new ReportHandler();
		EventHandler.print(rh.getOccupancyRate("2012-04-01","1"));
		//GUI_Event_Handler.print(rh.getMonthlyProfit("2012-04-01","2012-05-01"));
	}
}
